package tgc.edu.yzy.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import tgc.edu.yzy.bean.OpenLayout;
import tgc.edu.yzy.custom.BaseConfiguration;
import tgc.edu.yzy.custom.TimeUtils;
import tgc.edu.yzy.jdbc.entity.Support;


public class OpenLayoutJDBC {
	private static String a1="com.mysql.jdbc.Driver";
	private static String a2="jdbc:mysql://47.100.197.180:3306/cqrobot";
	private static String a3="qqrobot";
	private static String a4="936642284";
	
	public static String saveDl(String url) {
		 String abc = "";
	        final Connection conn = conn();
	        try {
	            final PreparedStatement prepareStatement = conn.prepareStatement("select COUNT(*) from dl_url");
	            final ResultSet executeQuery = prepareStatement.executeQuery();
	            if (executeQuery.next()) {
	                final Integer a = executeQuery.getInt(1);
	                abc = TimeUtils.AZ((int)a);
	                final String sql = "INSERT INTO dl_url VALUES (null,?,?,?,?,?)";
	                final CallableStatement prepareCall = conn.prepareCall(sql);
	                prepareCall.setString(1, TimeUtils.getStringTime());
	                prepareCall.setString(2, null);
	                prepareCall.setString(3, abc);
	                prepareCall.setString(4, "0");
	                prepareCall.setString(5, url);
	                prepareCall.execute();
	                prepareCall.close();
	            }
	            executeQuery.close();
	            prepareStatement.close();
	            conn.close();
	        }
	        catch (SQLException e) {
	            e.printStackTrace();
	        }
	        return abc;
	}
	
	public static Connection conn() {//链接数据库
		Connection connection=null;
		try {
			Class.forName(a1);//注册驱动
			connection =DriverManager.getConnection(a2,a3,a4);//建立链接
		}catch(Exception e) {
			System.out.println("数据库不存在，");
			return null;
		}
		return connection;
		
	}
	/***
	 * 取出所有阵型
	 * @return
	 */
	public static List<OpenLayout> list() {
		List<OpenLayout> ol = new ArrayList<>();
		String sql = " SELECT   t.*  from  open_layout t ";
		Connection conn = conn();
		try {
			PreparedStatement pr = conn.prepareStatement(sql);
			ResultSet rs = pr.executeQuery();
			while(rs.next()) {
				OpenLayout o = new OpenLayout();
				o.setId(rs.getInt(1));
				o.setCreateDate(rs.getString(2));
				o.setImageUrl(rs.getString(4));
				o.setNumber(rs.getString(5));
				o.setRemark("");
				o.setType(rs.getString(7));
				o.setUrl(rs.getString(8));
				o.setDldate(rs.getString(9));
				o.setDlurl(rs.getString(10));
				ol.add(o);
			}
			rs.close();
			pr.close();
			conn.close();
			return ol;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ol;
	}
	//保存
	public static void updateById(OpenLayout open) {
		String sql ="update open_layout SET dldate=?,dlurl=? where id=?";
		Connection conn = conn();
		try {
			CallableStatement pr = conn.prepareCall(sql);
			pr.setString(1, open.getDldate());
			pr.setString(2, open.getDlurl());
			pr.setInt(3, open.getId());
			pr.execute();
			pr.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static Support findSupportAll() {
		final Support s = new Support();
        final String sql = "select * from support";
        final Connection conn = conn();
        try {
            final PreparedStatement pr = conn.prepareStatement(sql);
            final ResultSet ex = pr.executeQuery();
            final List<String> grouplist = new ArrayList<String>();
            final List<String> qq = new ArrayList<String>();
            while (ex.next()) {
                final String group_list = ex.getString(5).replaceAll("[^0-9]", "");
                grouplist.add(group_list);
                final String q_q = ex.getString(6).replaceAll("[^0-9]", "");
                qq.add(q_q);
            }
            ex.close();
            pr.close();
            conn.close();
            return s;
        }
        catch (SQLException e) {
            e.printStackTrace();
            return s;
        }
	}
	
	/***
	 *
	 * @param message 解释
	 * @param sum 总数量
	 * @param type 类型
	 */
	public static void ImageSave(String message,Integer sum,String type) {
		String localQQCode = BaseConfiguration.getLocalQQCode();
		String sql = "INSERT INTO image_sum VALUES (null,'"+TimeUtils.getStringTime()+"' ,null,'"+localQQCode+",'"+message+"',"+sum+",'"+type+"')";
		final Connection conn = conn();
        try {
            final PreparedStatement pr = conn.prepareStatement(sql);
            pr.execute();
            pr.close();
            conn.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
	}
}
